import pymysql
from todo import Todo

class mysql:
    @staticmethod
    def get_connection(db='test'):
        return pymysql.Connection(host='192.168.116.128',database=db,user='root',password='Xl0317..',autocommit=True)
    def create(self,title:str):
        with self.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('insert into todos(title) value(%s)',(title,))
            cursor.execute('select Last_insert_id()')
            (todo_id,) = cursor.fetchone()
            return Todo(title,todo_id)


    def init_db(self):
        with self.get_connection(db=None)as x:
            cursor = x.cursor()
            create = '''CREATE TABLE if not exists todos(
                todo_id int PRIMARY KEY unique auto_increment NOT NULL,
                title varchar(100) DEFAULT '',
                completed int DEFAULT 0
                );'''


            result = cursor.execute(create)
            data = cursor.fetchall()
            print(data)


    def convert_row_to_object(self,i):
        return Todo(i[1],id=i[0],completed=True if i[2]==1 else False)


    def get_list(self):
        with self.get_connection() as x:
            cursor = x.cursor()
            cursor.execute('select * from todos')
            data = cursor.fetchall()
            list = []
            for i in data:
                list.append(self.convert_row_to_object(i))
            print(list)

    def get_by_id(self, todo_id):
        with self.get_connection() as x:
            cursor = x.cursor()
            cursor.executemany('select * from todos where todo_id = %s',(todo_id,))
            todo = cursor.fetchone()
            if todo is not None:
                return self.convert_row_to_object(todo)
            else:
                return None
    def mark_completed(self,todo_id):
        with self.get_connection()as conn:
            cursor = conn.cursor()
            cursor.execute('update todos set completed = 1 where todo_id = %s',(todo_id,))
            conn.commit()
            return self.get_by_id(todo_id)

qwe=mysql()
qwe.create('学习')
qwe.init_db()
qwe.get_list()
qwe.get_by_id(4)
print(qwe.mark_completed(5))
